 package cn.edu.zjgsu.ito.service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import cn.edu.zjgsu.ito.util.GetTimeUtil;

import com.itextpdf.text.Chunk;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfWriter;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminVisaManageService {

	/**
	 * 签证管理
	 * 杨昆鹏
	 * */
	private POIFSFileSystem fs;
	private HSSFWorkbook wb;
	private HSSFSheet sheet;
	private HSSFRow row;
	SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
	Calendar curr = Calendar.getInstance();
	GetTimeUtil getTimeUtil = new GetTimeUtil();

	//打印页面
	public Record stuVisaPrint(String passportNo,int time){
		Date date = new Date();
		//time=1 六个月，time=2 一年，time=3 期限离境
		Record stuInf = Db.findFirst("select * from student_info where passportNo=?",passportNo);

		String lastName = stuInf.getStr("lastName");
		String firstName = stuInf.getStr("firstName");

		Record visaStu = new Record().set("lastName", lastName).set("firstName",firstName)
				.set("passportNo",passportNo).set("time",time);

		//打印的数据存入数据库
		String visaDateStart = formatter.format(date);
		String visaDateEnd = "";
		String visaDate = "";
		String visaTime = "";
		switch(time){
		case 1:
			visaTime = "六个月";
			Calendar currs = Calendar.getInstance();
			currs.set(Calendar.MONTH,currs.get(Calendar.MONTH)+6);
			Date dates=currs.getTime();
			visaDateEnd = formatter.format(dates);
			visaDate = visaDateStart + "-" + visaDateEnd ;
			System.out.println(formatter.format(dates));
			break;
		case 2:
			visaTime = "一年";
			Calendar curr = Calendar.getInstance();
			curr.set(Calendar.YEAR,curr.get(Calendar.YEAR)+1);
			Date datea=curr.getTime();
			visaDateEnd = formatter.format(datea);
			visaDate = visaDateStart + "-" + visaDateEnd ;
			System.out.println(formatter.format(datea));
			break;
		case 3:
			visaTime = "期限离境";
			visaDate = "期限离境";
			break;
		}
		System.out.println(passportNo);
		System.out.println(visaDateStart);
		System.out.println(visaTime);

		String visaType = "普通签证";
		Record stuVisas = new Record().set("passportNo", passportNo)
				.set("visaDate",visaDate).set("visaTime",visaTime).set("visaType",visaType);
		System.out.println(visaType);
		//		Db.save("visa", stuVisas);
		return visaStu;
	}

	//签证管理页面
	public List<Record> stuVisaManage() throws ParseException{
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		List<Record> stuVisa = Db.find("select * from visa");
		List<Record> stuVisaInf = new ArrayList<Record>();

		for(int i = 0;i < stuVisa.size();i++){
			String passportNo = stuVisa.get(i).getStr("passportNo");//护照号
			Record stuInf = Db.findFirst("select * from student_info where passportNo=?",passportNo);
			String lastName = stuInf.getStr("lastName");
			String firstName = stuInf.getStr("firstName");
			String majorName = stuInf.getStr("majorName");
			String sNo = stuInf.getStr("sNo");

			String visaDate = stuVisa.get(i).getStr("visaDate");
			String visaTime = stuVisa.get(i).getStr("visaTime");
			String visaType = stuVisa.get(i).getStr("visaType");

			String []b= new String[100];
			Date []date=new Date[100];
			Date dates=new Date();
			String Dates="";
			String newpassportNos="";
			List<Record> stuVisas = Db.find("select * from newpassportno where passportNo=?",stuVisa.get(i).getStr("passportNo"));
			if(stuVisas.size()>1){
				for(int j=0;j<stuVisas.size();j++){
					b[j] = stuVisas.get(j).getStr("time");
					date[j] = formatter.parse(b[j]);
				}
				//								System.out.println(stuVisas.size());
				for(int a=0;a<stuVisas.size()-1;a++){
					dates = date[a];
					int c = (date[a+1]).compareTo(dates);
					//									System.out.println("-------");
					//									System.out.println(c);
					//									System.out.println("++++++++++++++++++++++++++++");
					if(c>0){
						dates = date[a+1];
					}
					//									System.out.println(dates);
					//									System.out.println("================");
				}
				//								System.out.println("qqqqqqqqq");
				//								System.out.println(dates);
				//								System.out.println("444444444");
				Dates = formatter.format(dates);
				//								System.out.println(Dates);
				Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
				//								System.out.println("111111111");
				//								System.out.println(newpassportNo.getStr("newpassportNo"));
				//								stuVisa.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
				newpassportNos = newpassportNo.getStr("newpassportNo");
			}
			else{
				//				System.out.println("[][][][][][][][][][]");
				Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",stuVisa.get(i).getStr("passportNo"));
				//				System.out.println("111111111");
				//				System.out.println(newpassportNo.getStr("newpassportNo"));
				//				stuVisa.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
				newpassportNos = newpassportNo.getStr("newpassportNo");
			}		

			Record stuVisass = new Record().set("passportNo", passportNo).set("lastName",lastName).set("firstName",firstName)
					.set("majorName",majorName).set("sNo",sNo)
					.set("visaDate",visaDate).set("visaTime",visaTime).set("visaType",visaType).set("newpassportNo", newpassportNos);

			stuVisaInf.add(stuVisass);
		}
		return stuVisaInf;
	}

	//签证批量上传
	public List<String> stuVisaUpload(InputStream is){
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
			String passportNo = "";
			String visaDateStart = "";
			String visaDateEnd = "";
			String visaDate = "";
			String succ = "";
			String error = "";
			int interror = 0;
			int intsucc = 0;
			sheet = wb.getSheetAt(0);
			row = sheet.getRow(0);
			List<Record> stuport = Db.find("select * from student_info");
			List<String> visaInfRet = new ArrayList<String>();
			int colNum = row.getPhysicalNumberOfCells();//总列数
			int rowNum = sheet.getPhysicalNumberOfRows();//总行数
			System.out.println(colNum+"=="+rowNum);

			for(int i =  2;i < rowNum;i++ ){
				String porterror = "";
				String nationerror = "";
				HSSFRow rowi = sheet.getRow(i);
				HSSFCell cell = null;

				for(int j = 1; j < colNum; j++){
					cell = rowi.getCell(j);//假如row.getCell(0)中的数值为12345678910123  
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
				}

				String newpassportNo = (sheet.getRow(i).getCell(1)).toString();
				Record stuInfo = Db.findFirst("select * from newpassportno where newpassportNo=?",newpassportNo);
				if(stuInfo != null){
					passportNo = stuInfo.getStr("passportNo");
				}
				String sNo = (sheet.getRow(i).getCell(2)).toString();
				//判断学生合法性
				int port = 0;
				int nation = 0;
				for(int p = 0;p < stuport.size();p++){
					if(passportNo.equals(stuport.get(p).getStr("passportNo"))){
						port = 1;
					}
					if(sNo.equals(stuport.get(p).getStr("sNo"))){
						nation = 1;
					}
				}
				if(port == 1){
					if(nation == 1){
						String temp = (sheet.getRow(i).getCell(3)).toString();
						visaDateStart = temp.replace(".","-");
						System.out.println(visaDateStart);
						String visaTime = (sheet.getRow(i).getCell(4)).toString();
						String visaType = (sheet.getRow(i).getCell(5)).toString();
						switch(visaTime){
						case  "六个月":
							Calendar currs = Calendar.getInstance();
							currs.set(Calendar.MONTH,currs.get(Calendar.MONTH)+6);
							Date datea=currs.getTime();
							visaDateEnd = formatter.format(datea);
							visaDate = visaDateStart +"-"+ visaDateEnd;
							System.out.println(formatter.format(datea));
							break;
						case "一年":
							Calendar curr = Calendar.getInstance();
							curr.set(Calendar.YEAR,curr.get(Calendar.YEAR)+1);
							Date dates=curr.getTime();
							visaDateEnd = formatter.format(dates);
							visaDate = visaDateStart +"-"+ visaDateEnd;
							System.out.println(formatter.format(dates));
							break;
						case "期限离境":
							visaDate = "期限离境";
							break;
						}
						System.out.println(passportNo+"==="+sNo+"==="+visaDateStart+"==="+visaTime+"==="+visaType);
						Record visaInf = new Record().set("passportNo", passportNo)
								.set("visaDate",visaDate).set("visaTime",visaTime).set("visaType",visaType);
						Db.save("visa", visaInf);
						intsucc++;
					}else{
						String temp = i + 1 +"";
						nationerror = "请核对" + temp + "行学生学号信息。";
						visaInfRet.add(nationerror);
						interror++;
						continue;
					}
				}else{
					String temp = i + 1 +"";
					porterror = "无第" + temp + "行学生护照信息。";
					visaInfRet.add(porterror);
					interror++;
					continue;
				}
			}
			if(visaInfRet.size() == 0){
				visaInfRet.add("全部上传成功。");
			}else{
				error = interror + "条失败。";
				succ = intsucc + "条成功。";
				visaInfRet.add(error);
				visaInfRet.add(succ);
			}
			return visaInfRet;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				is.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	/**
	 * 筛选
	 * 刘阳
	 */
	public List<Record> selectNew(String majorName,String eduStartDate,String eduSeason){
		int i;
		System.out.println("*****");
		System.out.println(majorName);
		System.out.println(eduStartDate);
		System.out.println(eduSeason);
		try {
			System.out.println("进来了");
			if( (!majorName.equals("0")) && (!eduStartDate.equals("0")) &&(!eduSeason.equals("0")) ){
				System.out.println("jianglaile");
				List<Record> liststudent=Db.find("select * from student_info where majorName=? and eduStartDate=? and eduSeason=?",majorName,eduStartDate,eduSeason);
				System.out.println(liststudent);
				List<Record> temp = new ArrayList<Record>();
				List<Record> list = new ArrayList<Record>();
				System.out.println("\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\");
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
					System.out.println("+++++++++++++++++++");
					System.out.println(temp);
				}
				for(int j=0;j<temp.size();j++){
					Record two = Db.findFirst("select * from visa where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(two);
					Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(lastName);
					Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(firstName);
					Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(majorName1);
					Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(sNo);
					System.out.println(")___________________________");
					System.out.println(list);
				}	
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
							b[jj] = payments.get(jj).getStr("time");
							date[jj] = formatter.parse(b[jj]);
						}
						System.out.println(payments.size());
						for(int a=0;a<payments.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						System.out.println("qqqqqqqqq");
						System.out.println(dates);
						System.out.println("444444444");
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}else if( (majorName.equals("0")) && (!eduStartDate.equals("0")) &&(!eduSeason.equals("0")) ){
				List<Record> liststudent=Db.find("select * from student_info where eduStartDate=? and eduSeason=?",eduStartDate,eduSeason);
				List<Record> temp = new ArrayList<Record>();
				List<Record> list = new ArrayList<Record>();
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					Record two = Db.findFirst("select * from visa where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(two);
					Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(lastName);
					Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(firstName);
					Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(majorName1);
					Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(sNo);
				}	
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
							b[jj] = payments.get(jj).getStr("time");
							date[jj] = formatter.parse(b[jj]);
						}
						System.out.println(payments.size());
						for(int a=0;a<payments.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						System.out.println("qqqqqqqqq");
						System.out.println(dates);
						System.out.println("444444444");
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}else if( (!majorName.equals("0")) && (eduStartDate.equals("0")) &&(!eduSeason.equals("0")) ){
				List<Record> liststudent=Db.find("select * from student_info where majorName=? and eduSeason=?",majorName,eduSeason);
				List<Record> temp = new ArrayList<Record>();
				List<Record> list = new ArrayList<Record>();
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					Record two = Db.findFirst("select * from visa where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(two);
					Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(lastName);
					Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(firstName);
					Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(majorName1);
					Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(sNo);
				}	
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
							b[jj] = payments.get(jj).getStr("time");
							date[jj] = formatter.parse(b[jj]);
						}
						System.out.println(payments.size());
						for(int a=0;a<payments.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						System.out.println("qqqqqqqqq");
						System.out.println(dates);
						System.out.println("444444444");
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}else if( (!majorName.equals("0")) && (!eduStartDate.equals("0")) &&(eduSeason.equals("0")) ){
				List<Record> liststudent=Db.find("select * from student_info where majorName=? and eduStartDate=? ",majorName,eduStartDate);
				List<Record> temp = new ArrayList<Record>();
				List<Record> list = new ArrayList<Record>();
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					Record two = Db.findFirst("select * from visa where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(two);
					Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(lastName);
					Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(firstName);
					Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(majorName1);
					Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(sNo);
				}
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
							b[jj] = payments.get(jj).getStr("time");
							date[jj] = formatter.parse(b[jj]);
						}
						System.out.println(payments.size());
						for(int a=0;a<payments.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						System.out.println("qqqqqqqqq");
						System.out.println(dates);
						System.out.println("444444444");
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}else if( (!majorName.equals("0")) && (eduStartDate.equals("0")) &&(eduSeason.equals("0")) ){
				List<Record> liststudent=Db.find("select * from student_info where majorName=? ",majorName);
				List<Record> temp = new ArrayList<Record>();
				List<Record> list = new ArrayList<Record>();
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					Record two = Db.findFirst("select * from visa where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(two);
					Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(lastName);
					Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(firstName);
					Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(majorName1);
					Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(sNo);
				}	
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
							b[jj] = payments.get(jj).getStr("time");
							date[jj] = formatter.parse(b[jj]);
						}
						System.out.println(payments.size());
						for(int a=0;a<payments.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						System.out.println("qqqqqqqqq");
						System.out.println(dates);
						System.out.println("444444444");
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}else if( (majorName.equals("0")) && (!eduStartDate.equals("0")) &&(eduSeason.equals("0")) ){
				List<Record> liststudent=Db.find("select * from student_info where eduStartDate=? ",eduStartDate);
				List<Record> temp = new ArrayList<Record>();
				List<Record> list = new ArrayList<Record>();
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					Record two = Db.findFirst("select * from visa where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(two);
					Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(lastName);
					Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(firstName);
					Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(majorName1);
					Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(sNo);
				}	
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
							b[jj] = payments.get(jj).getStr("time");
							date[jj] = formatter.parse(b[jj]);
						}
						System.out.println(payments.size());
						for(int a=0;a<payments.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						System.out.println("qqqqqqqqq");
						System.out.println(dates);
						System.out.println("444444444");
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}else if( (majorName.equals("0")) && (eduStartDate.equals("0")) &&(!eduSeason.equals("0")) ){
				List<Record> liststudent=Db.find("select * from student_info where eduSeason=?",eduSeason);
				List<Record> temp = new ArrayList<Record>();
				List<Record> list = new ArrayList<Record>();
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					Record two = Db.findFirst("select * from visa where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(two);
					Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(lastName);
					Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(firstName);
					Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(majorName1);
					Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",temp.get(j).getStr("passportNo"));
					list.add(sNo);
				}	
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
							b[jj] = payments.get(jj).getStr("time");
							date[jj] = formatter.parse(b[jj]);
						}
						System.out.println(payments.size());
						for(int a=0;a<payments.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}
			else{
				List<Record> list = Db.find("select * from visa");
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
							b[jj] = payments.get(jj).getStr("time");
							date[jj] = formatter.parse(b[jj]);
						}
						for(int a=0;a<payments.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							if(c>0){
								dates = date[a+1];
							}
						}
						Dates = formatter.format(dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
						Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("lastName",lastName.getStr("lastName"));
						Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("firstName",firstName.getStr("firstName"));
						Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("majorName",majorName1.getStr("majorName"));
						Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("sNo",sNo.getStr("sNo"));
					}
					else{
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
						Record lastName = Db.findFirst("select lastName from student_info where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("lastName",lastName.getStr("lastName"));
						Record firstName = Db.findFirst("select firstName from student_info where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("firstName",firstName.getStr("firstName"));
						Record majorName1 = Db.findFirst("select majorName from student_info where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("majorName",majorName1.getStr("majorName"));
						Record sNo = Db.findFirst("select sNo from student_info where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("sNo",sNo.getStr("sNo"));
					}	
				}
				System.out.println(list);
				return list;
			}

		}
		catch (Exception e) {
			System.out.println("来这里了");
			e.printStackTrace();
			return null;
		}
	}

	//获取所有学生个人信息
	public List<Record> stuInfo() {
		List<Record> stuInfo = Db.find("select * from student_info,newpassportNo where student_info.passportNo=newpassportNo.passportNo");
		return stuInfo;
	}

	//打印--存储签证信息
	public void addVisa(String passportNoes,String timemath) {
		String[] temp = passportNoes.split(",");
		String date = getTimeUtil.getTime2();
		String time = "";
		if(timemath.equals("1")){
			time = "六个月";
		}else if(timemath.equals("2")){
			time = "一年";
		}else if (timemath.equals("3")){
			time = "期限离境";
		}
		for(int i = 0;i < temp.length;i++){
			if(temp[i] != null){
				Record stu = new Record().set("passportNo",temp[i]).set("visaDate",date).set("visaTime",time).set("visaType","正常");
				Db.save("visa",stu);
			}
		}

	}

	//签证页面打印
	public void printVisa(OutputStream out,String passportNoes, String time) {
		String fontPdf = "";
		String timeStr = "";
		String[] passportNo = passportNoes.split(",");

		if(time.equals("1")){
			timeStr = "    √有效期6个月  □有效期1学年  □限期离境材料";
		}else if(time.equals("2")){
			timeStr = "    □有效期6个月  √有效期1学年  □限期离境材料";
		}else{
			timeStr = "    □有效期6个月  □有效期1学年  √限期离境材料";
		}

		String path = this.getClass().getResource("/").getPath();
		try {
			fontPdf = URLDecoder.decode(path,"UTF-8").replace("WEB-INF/classes/", "fonts/SIMHEI.TTF").substring(1);
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Document document = new Document(PageSize.A4);
		try {
			PdfWriter.getInstance(document,out);
			BaseFont bfHei = BaseFont.createFont(fontPdf, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
			Font font = new Font(bfHei,30);
			Font font2 = new Font(bfHei,20);

			document.open();
			for(int i = 0;i < passportNo.length;i++){
				Record stuInfo = Db.findFirst("select * from student_info where passportNo=?",passportNo[i]);
				Record stuNewpass = Db.findFirst("select * from newpassportNo where passportNo=? order by time desc ",passportNo[i]);

				document.add(new Paragraph(" "));
				Paragraph oneLine = new Paragraph("国际生签证办理联系单",font);
				oneLine.setAlignment(1);
				document.add(oneLine);

				document.add(new Paragraph(" "));

				Paragraph infoLine = new Paragraph("    兹有学生" + stuInfo.getStr("firstName") +" " + stuInfo.getStr("lastName") + "，护照号码：" + stuNewpass.getStr("newpassportNo") + "，请为其出具以下类型的签证材料：",font2);
				document.add(infoLine);

				Paragraph timeLine = new Paragraph(timeStr,font2);
				document.add(timeLine);

				document.add(new Paragraph(" "));

				Paragraph teaLine = new Paragraph("经办老师签名: ",font2);
				teaLine.setAlignment(Element.ALIGN_RIGHT);
				document.add(teaLine);
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));

				Paragraph lastLine = new Paragraph("计算机与信息工程学院(盖章)",font2);
				lastLine.setAlignment(Element.ALIGN_RIGHT);
				document.add(lastLine);

				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph("--------------------------------------------------------------------------------------------------------------------------------"));

				document.add(new Paragraph(" "));
				document.add(oneLine);

				document.add(new Paragraph(" "));

				document.add(infoLine);
				document.add(timeLine);

				document.add(new Paragraph(" "));

				document.add(teaLine);
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));

				document.add(lastLine);

				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.add(new Paragraph(" "));
				document.newPage();
			}
			document.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	}

	public List<Record> printSerch(String majorNo, String eduStartDate, String eduSeason) {
		
		List<Record> temp = new ArrayList<Record>();
		
		if(majorNo.equals("0") && eduStartDate.equals("0") && eduSeason.equals("0")){
		
			temp = Db.find("select * from student_info,visa,newpassportno where student_info.passportNo=visa.passportNo and student_info.passportNo=newpassportno.passportNo");
	
		}else if(majorNo.equals("0") && eduStartDate.equals("0")){
		
			temp = Db.find("select * from student_info,visa,newpassportno where student_info.passportNo=visa.passportNo and student_info.passportNo=newpassportno.passportNo and eduSeason=?",eduSeason);
	
		}else if(majorNo.equals("0") && eduSeason.equals("0")){
		
			temp = Db.find("select * from student_info,visa,newpassportno where student_info.passportNo=visa.passportNo and student_info.passportNo=newpassportno.passportNo and eduStartDate=?",eduStartDate);
	
		}else if(eduStartDate.equals("0") && eduSeason.equals("0")){
	
			temp = Db.find("select * from student_info,visa,newpassportno where student_info.passportNo=visa.passportNo and student_info.passportNo=newpassportno.passportNo and majorNo=?",majorNo);
	
		}else if(majorNo.equals("0")){
		
			temp = Db.find("select * from student_info,visa,newpassportno where student_info.passportNo=visa.passportNo and student_info.passportNo=newpassportno.passportNo and eduStartDate=? and eduSeason=?",eduStartDate,eduSeason);
	
		}else if(eduStartDate.equals("0")){
		
			temp = Db.find("select * from student_info,visa,newpassportno where student_info.passportNo=visa.passportNo and student_info.passportNo=newpassportno.passportNo and majorNo=? and eduSeason=?",majorNo,eduSeason);
	
		}else if(eduSeason.equals("0")){
			
			temp = Db.find("select * from student_info,visa,newpassportno where student_info.passportNo=visa.passportNo and student_info.passportNo=newpassportno.passportNo and majorNo=? and eduStartDate=?",majorNo,eduStartDate);
			
		}
		return temp;
	}
}
